package com.portal.logic;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

public class TypeDAO {

	//private String ip;
	private Connection connection;
	
	public TypeDAO(Connection connection) {
		//this.ip = ip;
		this.connection = connection;
	}

	

	//Create
	public void insert() {
		Type type = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\type.out");
			type = (Type) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
            st.executeUpdate("INSERT INTO tblTypes " + 
                "VALUES ("+type.getId()+", '"+type.getName()+ "',"+type.getIdField()+" )"); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}
	
	public int selectMaxId() {
		Statement statement = null;
		ResultSet resultSet = null;
		int id = 0;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT max(id) as max_val FROM tblTypes");

			Type type = null;
			while(resultSet.next()){
				id = Integer.parseInt(resultSet.getString("max_val"));
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		
		return id;
	}
	
	
	
	
	
	public Type selectById(int id) {
		Type type = new Type();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblTypes WHERE id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	type.setId(resultSet.getInt("id"));
            	type.setName(resultSet.getString("name"));
            	type.setIdField(resultSet.getInt("id_field"));
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return type;
    }

	public Type selectByName(String name) {
		Type type = new Type();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblTypes WHERE name = ?");
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	type.setId(resultSet.getInt("id"));
            	type.setName(resultSet.getString("name"));
            	type.setIdField(resultSet.getInt("id_field"));
            }
 
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return type;
    }
	
	//Read All
	public List<Type> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;
		List<Type> types = new LinkedList<Type>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM tblTypes");

			Type type = null;
			while(resultSet.next()){
				type = new Type();
				type.setId(Integer.parseInt(resultSet.getString("id")));
				type.setName(resultSet.getString("name"));
				type.setIdField(Integer.parseInt(resultSet.getString("id_field")));
				types.add(type);
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		return types;
	}

	public void update(/*Type type, int id*/) {
		Type type = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\editType.out");
			type = (Type) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
			String sql ="UPDATE tblTypes SET " + 
	                "name = '"+type.getName()+"', id_field="+type.getIdField()+" WHERE id="+type.getId();
            st.executeUpdate(sql); 
            
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}

	//Delete
	public void delete(int id) {
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("DELETE FROM tblTypes WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();


		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}
}
